#INCLUDE "Protheus.ch"

USER FUNCTION TstRelVen()

oReport := ReportDef()
oReport:PrintDialog()

RETURN NIL

//-----------------------------------------------------------------
STATIC FUNCTION ReportDef()
LOCAL aResumo := {}
LOCAL cPerg := "BETO"
LOCAL oReport := NIL
LOCAL oNota := NIL
LOCAL oItens := NIL
LOCAL oResumo := NIL

Pergunte(cPerg, .F.)
oReport := TReport():New("TstRelVen", "Produtos Vendidos por Cliente/Loja", cPerg, {|oReport| ReportPrint(oReport)}, OEMTOANSI("Este programa tem como objetivo gerar um relatorio de Clientes x Produtos."))
oReport:SetLandscape()

oNota := TRSection():New(oReport, "Nota", {"SD2", "SA1"}, /*aOrder*/, /*lLoadCells*/, /*lLoadOrder*/, /*uTotalText*/, /*lTotalInLine*/, /*lHeaderPage*/, /*lHeaderBreak*/, /*lPageBreak*/, /*lLineBreak*/, /*nLeftMargin*/, /*lLineStyle*/)
oNota:SetHeaderPage(.F.)
oNota:SetHeaderSection(.T.)
oNota:ForceLineStyle()
TRCell():New(oNota, "D2_EMISSAO", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_DOC", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_SERIE", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_CLIENTE", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_LOJA", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "A1_NOME", "SA1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "A1_END", "SA1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "A1_MUN", "SA1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "A1_EST", "SA1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_PEDIDO", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)

oItens := TRSection():New(oNota, "Itens", {"SD2", "SB1"})
oItens:SetHeaderSection(.T.)
oItens:SetTotalInLine(.F.)
TRCell():New(oItens, "nItem", "   ", "Item", "@!", 03,/*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oItens, "D2_COD", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "B1_DESC", "SB1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "nCaixas", "   ", "Caixas", "@E 99,999.99", 08, /*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "nFormas", "   ", "Formas", "@E 99,999.99", 08, /*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "nPesLiq", "   ", "Peso Liq.", "@E 999,999.999", 10,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "nPesBru", "   ", "Peso Brut.", "@E 999,999.999", 10,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "D2_TOTAL", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "D2_VALBRUT", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)

TRFunction():New(oItens:Cell("D2_COD"), NIL, "COUNT")
TRFunction():New(oItens:Cell("nCaixas"), NIL, "SUM")
TRFunction():New(oItens:Cell("nFormas"), NIL, "SUM")
TRFunction():New(oItens:Cell("nPesLiq"), NIL, "SUM")
TRFunction():New(oItens:Cell("nPesBru"), NIL, "SUM")
TRFunction():New(oItens:Cell("D2_TOTAL"), NIL, "SUM")
TRFunction():New(oItens:Cell("D2_VALBRUT"), NIL, "SUM")

oResumo := TRSection():New(oReport, "Resumo", {"SB1"})
oResumo:SetTotalInLine(.F.)
TRCell():New(oResumo, "cProd", "  ", "Produto", "@", 15, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "cDesc", "  ", "Descricao", "@", 30, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotCxs", "  ", "Caixas", "@E 999,999.99", 09, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotFrm", "  ", "Formas", "@E 999,999.99", 09, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotPesLiq", "  ", "Peso Liq.", "@E 999,999.999", 10, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotPesBru", "  ", "Peso Bruto", "@E 999,999.999", 10, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotVlr", "  ", "Valor", "@E 999,999,999.99", 12, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotVlrBru", "  ", "Valor Bruto", "@E 999,999,999.99", 12, /*lPixel*/, /*{|| code-block de impressao }*/)

TRFunction():New(oResumo:Cell("cProd"), NIL, "COUNT")
TRFunction():New(oResumo:Cell("nTotCxs"), NIL, "SUM")
TRFunction():New(oResumo:Cell("nTotFrm"), NIL, "SUM")
TRFunction():New(oResumo:Cell("nTotPesLiq"), NIL, "SUM")
TRFunction():New(oResumo:Cell("nTotPesBru"), NIL, "SUM")
TRFunction():New(oResumo:Cell("nTotVlr"), NIL, "SUM")
TRFunction():New(oResumo:Cell("nTotVlrBru"), NIL, "SUM")

RETURN (oReport)

//-----------------------------------------------------------------
STATIC FUNCTION ReportPrint(oReport)
LOCAL aResumo := {}
LOCAL cAlias := GetNextAlias()
LOCAL cProdDe := mv_par01
LOCAL cProdAte := mv_par02
LOCAL cCliDe := mv_par05
LOCAL cCliAte := mv_par06
LOCAL cLojaDe := mv_par07
LOCAL cLojaAte := mv_par08
LOCAL cMarca := mv_par09
LOCAL cProd := ""
LOCAL cDesc := ""
LOCAL cFiltro := "%%"
LOCAL cPeriodo := "%%"
LOCAL dEmisDe := mv_par03
LOCAL dEmisAte := mv_par04
LOCAL lMarca := !EMPTY(mv_par09)
LOCAL nCaixas := 0
LOCAL nFormas := 0
LOCAL nPesLiq := 0
LOCAL nPesBru := 0
LOCAL nItem := 0
LOCAL nRecCount := 0
LOCAL nPos := 0
LOCAL nTotCxs := 0
LOCAL nTotFrm := 0
LOCAL nTotPesLiq := 0
LOCAL nTotPesBru := 0
LOCAL nTotVlr := 0
LOCAL nTotVlrBru := 0
LOCAL nI := 0

IF !EMPTY(MV_PAR09)
	cFiltro := "%AND C5_MARCA = '" + RTRIM(MV_PAR09) + "'%"
ENDIF

cPeriodo :=	"%" + IF(MV_PAR10 == 1, "D2_EMISSAO", "C5_DTENTRG") + " BETWEEN '" + DTOS(dEmisDe) + "' AND '" + DTOS(dEmisAte) + "' %"

oReport:Section(1):BeginQuery()	
	BEGINSQL ALIAS cAlias
	SELECT D2_EMISSAO,
	       D2_DOC,
	       D2_SERIE,
	       D2_CLIENTE,
	       D2_LOJA,
	       A1_NOME,
	       A1_END,
	       A1_MUN,
	       A1_EST,
	       D2_COD,
	       SUM(D2_QUANT) D2_QUANT,
	       SUM(D2_TOTAL) D2_TOTAL,
	       SUM(D2_VALBRUT) D2_VALBRUT,
	       D2_PEDIDO,
	       C5_MARCA
	  FROM %Table:SD2% SD2
	 INNER JOIN %Table:SC5% SC5 ON C5_NUM = D2_PEDIDO
                      AND C5_FILIAL = %xFilial:SC5%
                      AND SC5.%NotDel%
	 INNER JOIN %Table:SA1% SA1 ON A1_COD = D2_CLIENTE
                      AND A1_LOJA = D2_LOJA
                      AND A1_FILIAL = %xFilial:SA1%
                      AND SA1.%NotDel%
	 WHERE %Exp:cPeriodo%
	   AND D2_COD BETWEEN %Exp:cProdDe% AND %Exp:cProdAte%
	   AND D2_CLIENTE BETWEEN %Exp:cCliDe% AND %Exp:cCliAte%
	   AND D2_LOJA BETWEEN %Exp:cLojaDe% AND %Exp:cLojaAte%
	   AND D2_FILIAL = %xFilial:SD2%
	   AND SD2.%NotDel%
	   %Exp:cFiltro%
	 GROUP BY D2_EMISSAO,
	          D2_DOC,
	          D2_SERIE,
	          D2_CLIENTE,
	          D2_LOJA,
	          A1_NOME,
	          A1_END,
	          A1_MUN,
	          A1_EST,
	          D2_COD,
	          D2_PEDIDO,
	          C5_MARCA
	 ORDER BY D2_DOC, D2_SERIE, D2_CLIENTE, D2_LOJA
	ENDSQL 
oReport:Section(1):EndQuery()

dbSelectArea(cAlias)
COUNT TO nRecCount
(cAlias)->(dbGoTop())
oReport:Section(1):Section(1):SetParentQuery()
oReport:SetMeter(nRecCount)
oReport:Section(1):Init()

dbSelectArea("SB1")
SB1->(dbSetOrder(1))

dbSelectArea("SC6")
SC6->(dbSetOrder(2)) // C6_FILIAL+C6_PRODUTO+C6_NUM+C6_ITEM

WHILE !(cAlias)->(EOF()) .And. !oReport:Cancel()
	oReport:IncMeter()
	oReport:Section(1):PrintHeader()
	oReport:Section(1):PrintLine()
	oReport:Section(1):Section(1):Init()
	cChave := (cAlias)->(D2_DOC + D2_SERIE + D2_CLIENTE + D2_LOJA)
	nItem := 0
	
	WHILE !(cAlias)->(EOF()) .And. !oReport:Cancel() .And. (cAlias)->(D2_DOC + D2_SERIE + D2_CLIENTE + D2_LOJA) == cChave
		nCaixas := 0
		nFormas := 0
		nPeso := 0
		SB1->(dbSeek(xFilial("SB1") + (cAlias)->D2_COD))
		
		IF RTRIM(SB1->B1_GRUPO) $ "3" .And. !(RTRIM((cAlias)->D2_COD) $ "310000/315500")
			IF SC6->(dbSeek(xFilial("SC6") + (cAlias)->D2_COD))
				nItem++
				nCaixas := SC6->C6_UNSVEN
				nFormas := SC6->C6_QTDPCT
				nPesLiq := SC6->C6_QTDVEN
				nPesBru := SC6->C6_QTDBRU
			ELSE
				nItem++
				nCaixas := 0
				nFormas := 0
				nPesLiq := 0
				nPesBru := 0
			ENDIF
		ELSE
			IF EMPTY(SB1->B1_CONV) .OR. EMPTY(SB1->B1_TIPCONV)
				nItem++
				nCaixas := (cAlias)->D2_QUANT
				nFormas := 0
				nPesLiq := 0
				nPesBru := 0
			ELSE
				nItem++
				nCaixas := IF(SB1->B1_TIPCONV $ "D", (cAlias)->D2_QUANT / SB1->B1_CONV, (cAlias)->D2_QUANT * SB1->B1_CONV)
				nFormas := 0
				nPesLiq := nCaixas * SB1->B1_PESO
				nPesBru := nCaixas * SB1->B1_PESBRU
			ENDIF
		ENDIF
		
		oReport:Section(1):Section(1):Cell("nItem"):SetBlock({|| STRZERO(nItem, 3)})
		oReport:Section(1):Section(1):Cell("nCaixas"):SetBlock({|| nCaixas})
		oReport:Section(1):Section(1):Cell("nFormas"):SetBlock({|| nFormas})
		oReport:Section(1):Section(1):Cell("nPesLiq"):SetBlock({|| nPesLiq})
		oReport:Section(1):Section(1):Cell("nPesBru"):SetBlock({|| nPesBru})
		oReport:Section(1):Section(1):PrintLine()
		
		nPos := aScan(aResumo, {|x|x[1] == (cAlias)->D2_COD})
		IF nPos > 0
			aResumo[nPos, 3] += nCaixas
			aResumo[nPos, 4] += nFormas
			aResumo[nPos, 5] += nPesLiq
			aResumo[nPos, 6] += nPesBru
			aResumo[nPos, 7] += (cAlias)->D2_TOTAL
			aResumo[nPos, 8] += (cAlias)->D2_VALBRUT
		ELSE
			AADD(aResumo, {(cAlias)->D2_COD, SB1->B1_DESC, nCaixas, nFormas, nPesLiq, nPesBru, (cAlias)->D2_TOTAL, (cAlias)->D2_VALBRUT})
		ENDIF
		(cAlias)->(dbSkip())
	END WHILE
	
	oReport:Section(1):Section(1):Finish()
	oReport:SkipLine()
	oReport:SkipLine()
	
END WHILE

oReport:Section(1):Finish()
(cAlias)->(dbCloseArea())

oReport:EndPage()
oReport:Section(2):Init()

aResumo := ASORT(aResumo, , , {|x, y| x[1] < y[1]})

FOR nI := 1 To LEN(aResumo)
	cProd := aResumo[nI, 1]
	cDesc := aResumo[nI, 2]
	nTotCxs := aResumo[nI, 3]
	nTotFrm := aResumo[nI, 4]
	nTotPesBru := aResumo[nI, 5]
	nTotPesLiq := aResumo[nI, 6]
	nTotVlr := aResumo[nI, 7]
	nTotVlrBru := aResumo[nI, 8]
	
	oReport:Section(2):Cell("cProd"):SetBlock({|| cProd})
	oReport:Section(2):Cell("cDesc"):SetBlock({|| cDesc})
	oReport:Section(2):Cell("nTotCxs"):SetBlock({|| nTotCxs})
	oReport:Section(2):Cell("nTotFrm"):SetBlock({|| nTotFrm})
	oReport:Section(2):Cell("nTotPesLiq"):SetBlock({|| nTotPesBru})
	oReport:Section(2):Cell("nTotPesBru"):SetBlock({|| nTotPesLiq})
	oReport:Section(2):Cell("nTotVlr"):SetBlock({|| nTotVlr})
	oReport:Section(2):Cell("nTotVlrBru"):SetBlock({|| nTotVlrBru})
	oReport:Section(2):PrintLine()
	
NEXT nI

oReport:Section(2):Finish()

RETURN NIL

//-----------------------------------------------------------------